package com.kexun.common.utils;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

public class ExcelUtils {

    public static List<List<String>> readExcel(InputStream inputStream, String fileType) {
        List<List<String>> lists = new ArrayList<List<String>>();
        //读取excel文件
        try {
            //获取工作薄
            Workbook wb = null;
            if (fileType.equals("xls")) {
                wb = new HSSFWorkbook(inputStream);
            } else if (fileType.equals("xlsx")) {
                wb = new XSSFWorkbook(inputStream);
            } else {
                throw new RuntimeException("文件格式错误");
            }

            //读取第一个工作页sheet
            Sheet sheet = wb.getSheetAt(0);
            //第一行为标题
            for (Row row : sheet) {
                ArrayList<String> list = new ArrayList<String>();
                short lastCellNum = row.getLastCellNum();
                for (int i = 0; i < lastCellNum; i++) {
                    Cell cell = row.getCell(i, Row.CREATE_NULL_AS_BLANK);
                    String cellValue = "";
                    switch (cell.getCellType()) {
                        // 数字类型 小数,整数,日期
                        case Cell.CELL_TYPE_NUMERIC:
                            if (cell.getCellStyle().getDataFormat() == 178) {
                                Date d = cell.getDateCellValue();
                                SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
                                cellValue = formatter.format(d);
                            } else if (cell.getCellStyle().getDataFormat() == 177) {
                                Date d = cell.getDateCellValue();
                                SimpleDateFormat formatter = new SimpleDateFormat("HH:mm:ss");
                                cellValue = formatter.format(d);
                            } else if (cell.getCellStyle().getDataFormat() == 176) {
                                Date d = cell.getDateCellValue();
                                SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                                cellValue = formatter.format(d);
                            } else {
                                DecimalFormat df = new DecimalFormat("0.00");
                                cellValue = df.format(cell.getNumericCellValue());
                            }
                            break;
                        // 字符串类型
                        case Cell.CELL_TYPE_STRING:
                            cellValue = cell.getStringCellValue();
                            break;
                        // 公式类型
                        case Cell.CELL_TYPE_FORMULA:
                            cellValue = String.valueOf(cell.getNumericCellValue());
                            break;
                        // 空白的单元格
                        case Cell.CELL_TYPE_BLANK:
                            break;
                        // 布尔类型
                        case Cell.CELL_TYPE_BOOLEAN:
                            break;
                        // 错误类型
                        case Cell.CELL_TYPE_ERROR:
                            break;
                        default:
                            break;
                    }
                    list.add(cellValue);
                }

                lists.add(list);
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                if (inputStream != null) {
                    inputStream.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return lists;
    }


    public static XSSFWorkbook createExcel(Column[] title, List<Map<String, Object>> data) {
        XSSFWorkbook wb = new XSSFWorkbook();
        // 2.在workbook中添加一个sheet,对应Excel文件中的sheet
        XSSFSheet sheet = wb.createSheet("sheet1");
        XSSFRow row = sheet.createRow(0);
        for (int i = 0; i < title.length; i++) {
            Column column = title[i];
            row.createCell(i).setCellValue(column.getTitle());
        }
        for (int i = 0; i < data.size(); i++) {
            Map<String, Object> map = data.get(i);
            row = sheet.createRow(i + 1);
            for (int j = 0; j < title.length; j++) {
                Column column = title[j];
                String property = column.getProperty();
                Object o = map.get(property);
                row.createCell(j).setCellValue(o == null ? "" : o.toString());
            }
        }
        return wb;
    }


}
